package managers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Vector;

import business.Currency;
import business.Current;
import business.Rate;

import conn.Conn;
import util.Codes;
import util.DFormat;
import util.JDate;

public class MngRates extends Manager {
	
	public MngRates() {

	}

	public boolean jSetRate(String date, int currency, float rate) {
		if (setRate(date, currency, rate)) return true;
		else return false;
	}
	
	public boolean setRate(String date, int currency, float rate) {
		return setRate(new JDate(date), currency, rate);
	}
	
	public boolean setRate(Calendar date, int currency, float rate) {
		return setRate(new JDate(date), currency, rate);
	}
	
	public boolean setRate(JDate date, int currency, float rate) {
		Rate objRate = new Rate();
		objRate.setDate(date);
		
		Currency objCurrency = new Currency();
		objCurrency.setId(currency);
		
		objRate.setCurrency(objCurrency);
		objRate.setRate(rate);
		
		return setRate(objRate);
		
	}
	
	public boolean setRate(Rate rate) {
		boolean res = false;
		
		String sql;
		
		try {
			if (getRate(rate) > 0) {
				sql = "UPDATE rates SET rate = " + rate.getRate() + " WHERE date = '" + rate.getDate().getDate(JDate.MySQL) + "' AND currency = " + rate.getCurrency().getId();
			}	else {
				sql = "INSERT rates (date, currency, rate) VALUES ('" + rate.getDate().getDate(JDate.MySQL) + "', " + rate.getCurrency().getId() + ", " + rate.getRate() + ")";
			}
			//System.out.print(sql);
			Statement stmt = Conn.getConn().createStatement();
			if (stmt.executeUpdate(sql) > 0) res = true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return res;
	}

	public float getRate(JDate date, Currency currency) {
		Rate rate = new Rate();
		rate.setDate(date);
		rate.setCurrency(currency);
		
		return getRate(rate);
	}
	
	public float getRate(JDate date, int currency) {
		Rate rate = new Rate();
		rate.setDate(date);
		
		Currency objCurrency = new Currency();
		objCurrency.setId(currency);
		
		rate.setCurrency(objCurrency);
		
		return getRate(rate);
	}
	
	
	public float getRate(String date, int currency) {
		Rate rate = new Rate();
		rate.setDate(new JDate(date));
		
		Currency objCurrency = new Currency();
		objCurrency.setId(currency);
		
		rate.setCurrency(objCurrency);
		
		return getRate(rate);
	}
	
	
	public float getRate(Calendar date, int currency) {
		return getRate(new JDate(date), currency);
	}
	
	public float getRate(Rate rate) {
		float rat = 0;
		
		String sql = "SELECT rate FROM rates WHERE date = '" + rate.getDate().getDate(JDate.MySQL) + "' AND currency = " + rate.getCurrency().getId();
		//System.out.println(sql);
		try {
			Statement stmt = Conn.getConn().createStatement();
			ResultSet rst = stmt.executeQuery(sql);
			
			if (rst.next()) rat = rst.getFloat("rate");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return rat;
	}
	
	public float getRate() {
		JDate date = new JDate();
		int currrencyID = 2;	// Dólares
		
		return getRate(date, currrencyID);
	}
	
	public float getRate(String date) {
		return getRate(date, 2);
	}
	
	public Vector<Rate> getRates(JDate dateFrom, JDate dateUntil) {
		Currency currency = new Currency();
		currency.setId(2);
		return getRates(dateFrom, dateUntil, currency);
	}
	
	public Vector<Rate> getRates(JDate dateFrom, JDate dateUntil, Currency currency) {
		Vector<Rate> rates = new Vector<Rate>();
		
		String sql = "SELECT date, currency, rate FROM rates WHERE date BETWEEN '" + dateFrom.getDate(JDate.MySQL) + "'" + 
					 " AND '" + dateUntil.getDate(JDate.MySQL) + "'" +
					 " AND currency = " + currency.getId();
		//System.out.println(sql);
		Statement stmt;
		try {
			stmt = Conn.getConn().createStatement();
			ResultSet rst = stmt.executeQuery(sql);
			
			while (rst.next()) {
				Rate rate = new Rate();
				rate.setDate(new JDate(rst.getString("date")));
				rate.setCurrency(currency);
				rate.setRate(rst.getFloat("rate"));
				
				rates.add(rate);
			}
			
		} catch (SQLException e) {
			rates = null;
			e.printStackTrace();
		}
		
		return rates;
	}
	
	public boolean verifyRate(String date, int currency, float rate) {
		return verifyRate(new JDate(date), currency, rate);
	}
	
	public boolean verifyRate(Calendar date, int currency, float rate) {
		return verifyRate(new JDate(date), currency, rate);
	}
	
	public boolean verifyRate(JDate date, int currency, float rate) {
		boolean rst = false;
		
		if (getRate(date, currency) > 0) rst = true;
		else {
			rst = setRate(date, currency, rate);
		}
		
		return rst;
	}

	public String getTable(String begin, String ending, int currency) {
		String sql, tbl = "";
		
		sql = "SELECT date, rate FROM rates WHERE date >= '" + begin + "' AND date <= '" + ending + "' AND currency = " + currency;
		
		System.out.println("getTblRates: " + sql);
		try {
			rst = stmt.executeQuery(sql);
			while (rst.next()) {
				String date = Codes.setFecha(rst.getString("date")); 
				tbl +=	"<tr>" +
							"<td>" + date + "</td>" +
							"<td align=\"right\">" + DFormat.format(rst.getFloat("rate"), 3) + "</td>" +
							"<td class=\"tdButt\">" +
								"<a href=\"JavaScript:;\" title=\"Modificar\" onclick=\"editRate('"+ date + "', " + rst.getFloat("rate") + ");\">" +
									"<img src=\"imgs/page_white_edit.png\" alt=\"Modificar\">" +
								"</a>" +
							"</td>" +
							
						"</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return tbl;
	}
	
	public boolean saveAperturaCaja(String date, float siS, float siD) {
		String sql;
		
		sql = "	UPDATE rates SET" +
			  "		saldoInicialS = " + siS + "," +
			  "		saldoInicialD = " + siD + "," +
			  "		situacion = 'A'" +		
			  "	WHERE date = '" + date + "'";
		
		return execUpdate(sql);
	}
	
	public boolean saveCierreCaja(String date, float sfS, float sfD) {
		String sql;
		
		sql = "	UPDATE rates SET" +
			  "		saldoFinalS = " + sfS + "," +
			  "		saldoFinalD = " + sfD + ", " +
			  "		situacion = 'C'" +
			  "	WHERE date = '" + date + "'";
		
		return execUpdate(sql);
	}
	
	public boolean setSituacion(String date, String situacion) {
		String sql = "UPDATE rates SET situacion = '" + situacion + "' WHERE date = '" + date + "'";
		
		return execUpdate(sql);
	}
	
	public String getTblApertura(int anio, int mes) {
		String sql, sqlp, tbl = "";
		
		sqlp = "SELECT prf.id " +
			   "FROM profiles prf " +
			   "INNER JOIN users usr ON prf.id = usr.profile " +
			   "WHERE usr.id = " + Current.user;
		System.out.println("getProfile: " + sqlp);
		
		sql = "	SELECT date, rate, saldoInicialS, saldoInicialD, saldoFinalS, saldoFinalD, situacion AS situacion " +
			  "	FROM rates " +
			  "	WHERE YEAR(date) = " + anio + " AND MONTH(date) = " + mes + 
			  "	ORDER BY date DESC";
		System.out.println("getTblApertura: " + sql);
		try {
			int profile = 0;
			rst = stmt.executeQuery(sqlp);
			if (rst.next()) profile = rst.getInt("id");
			
			rst = stmt.executeQuery(sql);
			while (rst.next()) {
				String sit = rst.getString("situacion");
				tbl += 	"<tr>" +
							"<td>" + Codes.setFecha(rst.getString("date")) + "</td>" +
							"<td align=\"right\">" + DFormat.format(rst.getFloat("rate"), 3) + "</td>" +
							"<td align=\"right\">" + (rst.getFloat("saldoInicialS") > 0 ? DFormat.format(rst.getFloat("saldoInicialS"), 2) : "") + "</td>" +
							"<td align=\"right\">" + (rst.getFloat("saldoInicialD") > 0 ? DFormat.format(rst.getFloat("saldoInicialD"), 2) : "") + "</td>" +
							"<td align=\"right\">" + (rst.getFloat("saldoFinalS") > 0 ? DFormat.format(rst.getFloat("saldoFinalS"), 2) : "") + "</td>" +
							"<td align=\"right\">" + (rst.getFloat("saldoFinalD") > 0 ? DFormat.format(rst.getFloat("saldoFinalD"), 2) : "") + "</td>" +
							"<td align=\"center\">" + 
								(profile != 1 ? (sit.equals("A") ? "Abierta" : "Cerrada") :
								"<select onchange=\"setACSituacion('" + rst.getString("date") + "', this.value);\">" +
									"<option value=\"A\"" + (sit.equals("A") ? " selected=\"selected\"" : "") + ">Abierta</option>" +
									"<option value=\"C\"" + (sit.equals("C") ? " selected=\"selected\"" : "") + ">Cerrada</option>" +
								"</select>") +
							"</td>" +
							"<td class=\"tdButt\">" +
								"<a href=\"JavaScript:;\" title=\"Modificar\" onclick=\"editAC(this);\">" + 
									"<img src=\"imgs/page_white_edit.png\" alt=\"Modificar\">" +
								"</a>" +
							"</td>" +
						"</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return tbl;
	}
}